/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package databeans;

import beans.UsuarioBean;
import com.sun.rowset.CachedRowSetImpl;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.sql.rowset.CachedRowSet;
import utils.CargarConfiguraciones;
/**
 *
 * @author Eduardo Cerritos
 */
public class UsuarioDataBean {

    private CachedRowSet rowSet;
    private CargarConfiguraciones cc;
    private int pagesize;

    public UsuarioDataBean() throws Exception{
        cc = new CargarConfiguraciones();
        Class.forName(cc.getValor("driver"));
        rowSet = new CachedRowSetImpl();
        rowSet.setUrl(cc.getValor("urlbase"));
        rowSet.setUsername(cc.getValor("usuario"));
        rowSet.setPassword(cc.getValor("clave"));
        pagesize = Integer.parseInt(cc.getValor("paginacion"));
    }

    public void setUsuario(UsuarioBean usuario) throws SQLException{
        String sql="INSERT INTO users (" +
                        "username, " +
                        "password, " +
                        "enabled) " +
                "VALUES (" +
                        "'" + usuario.getUsuario() + "'," +
                        "'" + usuario.getClave() + "', " +
                        usuario.getActivo() + ")";
        rowSet.setCommand(sql);
        rowSet.execute();
        sql = "INSERT INTO authorities(" +
              "username, authority) " +
              "VALUES( " +
              "'" + usuario.getUsuario() + "'," +
              "'" + usuario.getRol() + "')";
        rowSet.setCommand(sql);
        rowSet.execute();

    }

    public void updateUsuario(UsuarioBean usuario) throws SQLException {
        String sql="UPDATE users SET " +
                "password = '" + usuario.getClave() + "', " +
                "enabled = " + usuario.getActivo() + " " +
                "WHERE username = '" + usuario.getUsuario() + "'";
        System.out.println(sql);
        rowSet.setCommand(sql);
        rowSet.execute();
        sql = "UPDATE authorities SET " +
              "authority = '" + usuario.getRol() + "' " +
              "WHERE username = '" + usuario.getUsuario() + "'";
        System.out.println(sql);
        rowSet.setCommand(sql);
        rowSet.execute();
    }

    public UsuarioBean getUsuario(String username){
        UsuarioBean usuario = new UsuarioBean();
        try{
             String sql=  "select " +
                    "u.username, " +
                    "u.password, " +
                    "u.enabled, " +
                    "a.authority " +
                    "from users u join authorities a using(username) " +
                    "where username='" +username+"'";
            System.out.println(sql);
            rowSet.setCommand(sql);
            rowSet.execute();
            rowSet.beforeFirst();
            rowSet.next();

            usuario.setUsuario(rowSet.getString(1));
            usuario.setClave(rowSet.getString(2));
            if(rowSet.getString(3).charAt(0) == 't')
                usuario.setActivo('1');
            else
                usuario.setActivo('0');
            usuario.setRol(rowSet.getString(4));

        }catch(SQLException e)
        {
            e.printStackTrace();
        }
    return usuario;
    }

  public ArrayList<UsuarioBean> getListaUsuarios() throws SQLException{
        ArrayList<UsuarioBean> usuarios = new ArrayList<UsuarioBean>();
        String sql = "select " + "u.username, " +
                "u.password, " + "u.enabled, " +
                "a.authority " + "from users u join authorities a using (username) " +
                "order by u.enabled, a.authority, u.username";
        System.out.println(sql);
        rowSet.setCommand(sql);
        rowSet.execute();
        rowSet.beforeFirst();
        while (rowSet.next()) {
            UsuarioBean usuario = new UsuarioBean();
            usuario.setUsuario(rowSet.getString(1));
            usuario.setClave(rowSet.getString(2));
            usuario.setActivo(rowSet.getString(3).charAt(0));
            usuario.setRol(rowSet.getString(4));
            usuarios.add(usuario);
        }
        return usuarios;
    }

  public ArrayList<UsuarioBean> getListaUsuarios(int pag) throws SQLException{
        ArrayList<UsuarioBean> usuarios = new ArrayList<UsuarioBean>();
        String sql = "select " + "u.username, " +
                "u.password, " + "u.enabled, " +
                "a.authority " + "from users u join authorities a using (username) " +
                "order by u.enabled, a.authority, u.username " +
                "limit " + pagesize*pag + ", " + pagesize;
        System.out.println(sql);
        rowSet.setCommand(sql);
        rowSet.execute();
        rowSet.beforeFirst();
        while (rowSet.next()) {
            UsuarioBean usuario = new UsuarioBean();
            usuario.setUsuario(rowSet.getString(1));
            usuario.setClave(rowSet.getString(2));
            usuario.setActivo(rowSet.getString(3).charAt(0));
            usuario.setRol(rowSet.getString(4));
            usuarios.add(usuario);
        }
        return usuarios;
    }

  public int getConteoUsuarios() throws SQLException{
      String sql = "SELECT COUNT(*) FROM users";
        rowSet.setCommand(sql);
        rowSet.execute();
        rowSet.beforeFirst();
        rowSet.next();
        return rowSet.getInt(1);
  }

}
